/**
 * Copyright (c) 2013-2022, Alibaba Group Holding Limited;
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * </p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.aliyun.polardbx.cdc.qatest.random;

import org.apache.commons.lang3.RandomUtils;

/**
 * created by ziyang.lb
 **/
public class SqlConstants {
    public static final String T_RANDOM_TABLE_PREFIX = "t_random_";
    public static final String T_RANDOM_CREATE_SQL = "CREATE TABLE IF NOT EXISTS `%s`\n" +
        "(`id` bigint(20) NOT NULL AUTO_INCREMENT,\n"
        + "`c_bit_1` bit(1) DEFAULT b'1',\n"
        + "`c_bit_8` bit(8) DEFAULT b'11111111',\n"
        + "`c_bit_16` bit(16) DEFAULT b'1111111111111111',\n"
        + "`c_bit_32` bit(32) DEFAULT b'11111111111111111111111111111111',\n"
        + "`c_bit_64` bit(64) DEFAULT b'1111111111111111111111111111111111111111111111111111111111111111',\n"
        + "`c_bit_hex_8` bit(8) DEFAULT 0xFF,\n"
        + "`c_bit_hex_16` bit(16) DEFAULT 0xFFFF,\n"
        + "`c_bit_hex_32` bit(32) DEFAULT 0xFFFFFFFF,\n"
        + "`c_bit_hex_64` bit(64) DEFAULT 0xFFFFFFFFFFFFFFFF,\n"
        + "`c_boolean` tinyint(1) DEFAULT true,\n"
        + "`c_boolean_2` tinyint(1) DEFAULT false,\n"
        + "`c_boolean_3` boolean DEFAULT false,\n"
        + "`c_tinyint_1` tinyint(1) DEFAULT 127,\n"
        + "`c_tinyint_4` tinyint(4) DEFAULT -128,\n"
        + "`c_tinyint_8` tinyint(8) DEFAULT -75,\n"
        + "`c_tinyint_3_un` tinyint(3) UNSIGNED DEFAULT 0,\n"
        + "`c_tinyint_8_un` tinyint(8) unsigned DEFAULT 255,\n"
        + "`c_tinyint_df_un` tinyint unsigned DEFAULT 255,\n"
        + "`c_tinyint_zerofill_un` tinyint(3) UNSIGNED ZEROFILL DEFAULT 255,\n"
        + "`c_smallint_1` smallint(1) DEFAULT 14497,\n"
        + "`c_smallint_2` smallint(2) DEFAULT 111,\n"
        + "`c_smallint_6` smallint(6) DEFAULT -32768,\n"
        + "`c_smallint_16` smallint(16) DEFAULT 32767,\n"
        + "`c_smallint_16_un` smallint(16) unsigned DEFAULT 65535,\n"
        + "`c_smallint_df_un` smallint unsigned DEFAULT 65535,\n"
        + "`c_smallint_zerofill_un` smallint(5) UNSIGNED ZEROFILL DEFAULT 65535,\n"
        + "`c_mediumint_1` mediumint(1) DEFAULT -8388608,\n"
        + "`c_mediumint_3` mediumint(3) DEFAULT 3456789,\n"
        + "`c_mediumint_9` mediumint(9) DEFAULT 8388607,\n"
        + "`c_mediumint_24` mediumint(24) DEFAULT -1845105,\n"
        + "`c_mediumint_8_un` mediumint(8) UNSIGNED DEFAULT 16777215,\n"
        + "`c_mediumint_24_un` mediumint(24) unsigned DEFAULT 16777215,\n"
        + "`c_mediumint_df_un` mediumint unsigned DEFAULT 16777215,\n"
        + "`c_mediumint_zerofill_un` mediumint(8) UNSIGNED ZEROFILL DEFAULT 7788,\n"
        + "`c_int_1` int(1) DEFAULT -2147483648,\n"
        + "`c_int_4` int(4) DEFAULT 872837,\n"
        + "`c_int_11` int(11) DEFAULT 2147483647,\n"
        + "`c_int_32` int(32) DEFAULT -2147483648,\n"
        + "`c_int_32_un` int(32) unsigned DEFAULT 4294967295,\n"
        + "`c_int_df_un` int unsigned DEFAULT 4294967295,\n"
        + "`c_int_zerofill_un` int(10) UNSIGNED ZEROFILL DEFAULT 4294967295,\n"
        + "`c_bigint_1` bigint(1) DEFAULT -816854218224922624,\n"
        + "`c_bigint_20` bigint(20) DEFAULT -9223372036854775808,\n"
        + "`c_bigint_64` bigint(64) DEFAULT 9223372036854775807,\n"
        + "`c_bigint_20_un` bigint(20) UNSIGNED DEFAULT 9223372036854775808,\n"
        + "`c_bigint_64_un` bigint(64) unsigned DEFAULT 18446744073709551615,\n"
        + "`c_bigint_df_un` bigint unsigned DEFAULT 18446744073709551615,\n"
        + "`c_bigint_zerofill_un` bigint(20) UNSIGNED ZEROFILL DEFAULT 1,\n"
        + "`c_tinyint_hex_1` tinyint(1) DEFAULT 0x3F,\n"
        + "`c_tinyint_hex_4` tinyint(4) DEFAULT 0x4F,\n"
        + "`c_tinyint_hex_8` tinyint(8) DEFAULT 0x5F,\n"
        + "`c_tinyint_hex_3_un` tinyint(3) UNSIGNED DEFAULT 0x2F,\n"
        + "`c_tinyint_hex_8_un` tinyint(8) unsigned DEFAULT 0x4E,\n"
        + "`c_tinyint_hex_zerofill_un` tinyint(3) UNSIGNED ZEROFILL DEFAULT 0x3F,\n"
        + "`c_smallint_hex_1` smallint(1) DEFAULT 0x2FFF,\n"
        + "`c_smallint_hex_2` smallint(2) DEFAULT 0x3FFF,\n"
        + "`c_smallint_hex_6` smallint(6) DEFAULT 0x4FEF,\n"
        + "`c_smallint_hex_16` smallint(16) DEFAULT 0x5EDF,\n"
        + "`c_smallint_hex_16_un` smallint(16) unsigned DEFAULT 0x7EDF,\n"
        + "`c_smallint_hex_zerofill_un` smallint(5) UNSIGNED ZEROFILL DEFAULT 0x8EFF,\n"
        + "`c_mediumint_hex_1` mediumint(1) DEFAULT 0x9EEE,\n"
        + "`c_mediumint_hex_3` mediumint(3) DEFAULT 0x7DDD,\n"
        + "`c_mediumint_hex_9` mediumint(9) DEFAULT 0x6CCC,\n"
        + "`c_mediumint_hex_24` mediumint(24) DEFAULT 0x5FCC,\n"
        + "`c_mediumint_hex_8_un` mediumint(8) UNSIGNED DEFAULT 0xFCFF,\n"
        + "`c_mediumint_hex_24_un` mediumint(24) unsigned DEFAULT 0xFCFF,\n"
        + "`c_mediumint_hex_zerofill_un` mediumint(8) UNSIGNED ZEROFILL DEFAULT 0xFFFF,\n"
        + "`c_int_hex_1` int(1) DEFAULT 0xFFFFFF,\n"
        + "`c_int_hex_4` int(4) DEFAULT 0xEFFFFF,\n"
        + "`c_int_hex_11` int(11) DEFAULT 0xEEFFFF,\n"
        + "`c_int_hex_32` int(32) DEFAULT 0xEEFFFF,\n"
        + "`c_int_hex_32_un` int(32) unsigned DEFAULT 0xFFEEFF,\n"
        + "`c_int_hex_zerofill_un` int(10) UNSIGNED ZEROFILL DEFAULT 0xFFEEFF,\n"
        + "`c_bigint_hex_1` bigint(1) DEFAULT 0xFEFFFFFFFEFFFF,\n"
        + "`c_bigint_hex_20` bigint(20) DEFAULT 0xFFFFFFFFFEFFFF,\n"
        + "`c_bigint_hex_64` bigint(64) DEFAULT 0xEFFFFFFFFEFFFF,\n"
        + "`c_bigint_hex_20_un` bigint(20) UNSIGNED DEFAULT 0xCFFFFFFFFEFFFF,\n"
        + "`c_bigint_hex_64_un` bigint(64) unsigned DEFAULT 0xAFFFFFFFFEFFFF,\n"
        + "`c_bigint_hex_zerofill_un` bigint(20) UNSIGNED ZEROFILL DEFAULT 0x1,\n"
        + "`c_decimal_hex` decimal DEFAULT 0xFFFFFF,\n"
        + "`c_decimal_hex_pr` decimal(10,3) DEFAULT 0xEFFF,\n"
        + "`c_decimal_hex_un` decimal(10,0) UNSIGNED DEFAULT 0xFFFF,\n"
        + "`c_float_hex` float DEFAULT 0xEFFF,\n"
        + "`c_float_hex_pr` float(10,3) DEFAULT 0xEEEE,\n"
        + "`c_float_hex_un` float(10,3) unsigned DEFAULT 0xFFEF,\n"
        + "`c_double_hex` double DEFAULT 0xFFFFEFFF,\n"
        + "`c_double_hex_pr` double(10,3) DEFAULT 0xFFFF,\n"
        + "`c_double_hex_un` double(10,3) unsigned DEFAULT 0xFFFF,\n"
        + "`c_tinyint_hex_x_1` tinyint(1) DEFAULT x'1F',\n"
        + "`c_tinyint_hex_x_4` tinyint(4) DEFAULT x'2F',\n"
        + "`c_tinyint_hex_x_8` tinyint(8) DEFAULT x'3F',\n"
        + "`c_tinyint_hex_x_3_un` tinyint(3) UNSIGNED DEFAULT x'FF',\n"
        + "`c_tinyint_hex_x_8_un` tinyint(8) unsigned DEFAULT x'EE',\n"
        + "`c_tinyint_hex_x_zerofill_un` tinyint(3) UNSIGNED ZEROFILL DEFAULT x'FF',\n"
        + "`c_smallint_hex_x_1` smallint(1) DEFAULT x'1FFF',\n"
        + "`c_smallint_hex_x_2` smallint(2) DEFAULT x'1FFF',\n"
        + "`c_smallint_hex_x_6` smallint(6) DEFAULT x'2FEF',\n"
        + "`c_smallint_hex_x_16` smallint(16) DEFAULT x'1EDF',\n"
        + "`c_smallint_hex_x_16_un` smallint(16) unsigned DEFAULT x'1EDF',\n"
        + "`c_smallint_hex_x_zerofill_un` smallint(5) UNSIGNED ZEROFILL DEFAULT x'5EFF',\n"
        + "`c_mediumint_hex_x_1` mediumint(1) DEFAULT x'4EEE',\n"
        + "`c_mediumint_hex_x_3` mediumint(3) DEFAULT x'3DDD',\n"
        + "`c_mediumint_hex_x_9` mediumint(9) DEFAULT x'2CCC',\n"
        + "`c_mediumint_hex_x_24` mediumint(24) DEFAULT x'1FCC',\n"
        + "`c_mediumint_hex_x_8_un` mediumint(8) UNSIGNED DEFAULT x'FAFF',\n"
        + "`c_mediumint_hex_x_24_un` mediumint(24) unsigned DEFAULT x'FAFF',\n"
        + "`c_mediumint_hex_x_zerofill_un` mediumint(8) UNSIGNED ZEROFILL DEFAULT x'FAFF',\n"
        + "`c_int_hex_x_1` int(1) DEFAULT x'FFFFFF',\n"
        + "`c_int_hex_x_4` int(4) DEFAULT x'FFFFFF',\n"
        + "`c_int_hex_x_11` int(11) DEFAULT x'FFFFFF',\n"
        + "`c_int_hex_x_32` int(32) DEFAULT x'FFFFFF',\n"
        + "`c_int_hex_x_32_un` int(32) unsigned DEFAULT x'FFFFFF',\n"
        + "`c_int_hex_x_zerofill_un` int(10) UNSIGNED ZEROFILL DEFAULT x'FFFFFF',\n"
        + "`c_bigint_hex_x_1` bigint(1) DEFAULT x'FFFFFFFFFFFFFF',\n"
        + "`c_bigint_hex_x_20` bigint(20) DEFAULT x'FFFFFFFFFFFFFF',\n"
        + "`c_bigint_hex_x_64` bigint(64) DEFAULT x'FFFFFFFFFFFFFF',\n"
        + "`c_bigint_hex_x_20_un` bigint(20) UNSIGNED DEFAULT x'FFFFFFFFFFFFFF',\n"
        + "`c_bigint_hex_x_64_un` bigint(64) unsigned DEFAULT x'FFFFFFFFFFFFFF',\n"
        + "`c_bigint_hex_x_zerofill_un` bigint(20) UNSIGNED ZEROFILL DEFAULT x'F1AB',\n"
        + "`c_decimal_hex_x` decimal DEFAULT x'FFFFFFFF',\n"
        + "`c_decimal_hex_x_pr` decimal(10,3) DEFAULT x'FFFF',\n"
        + "`c_decimal_hex_x_un` decimal(10,0) UNSIGNED DEFAULT x'FFFF',\n"
        + "`c_float_hex_x` float DEFAULT x'FFFF',\n"
        + "`c_float_hex_x_pr` float(10,3) DEFAULT x'EEEE',\n"
        + "`c_float_hex_x_un` float(10,3) unsigned DEFAULT x'FFFF',\n"
        + "`c_double_hex_x` double DEFAULT x'FFFFEFFF',\n"
        + "`c_double_hex_x_pr` double(10,3) DEFAULT x'FFFF',\n"
        + "`c_double_hex_x_un` double(10,3) unsigned DEFAULT x'FFFF',\n"
        + "`c_decimal` decimal DEFAULT -1613793319,\n"
        + "`c_decimal_pr` decimal(10,3) DEFAULT 1223077.292,\n"
        + "`c_decimal_un` decimal(10,0) UNSIGNED DEFAULT 10234273,\n"
        + "`c_numeric_df` numeric DEFAULT 10234273,\n"
        + "`c_numeric_10` numeric(10,5) DEFAULT 1,\n"
        + "`c_numeric_df_un` numeric UNSIGNED DEFAULT 10234273,\n"
        + "`c_numeric_un` numeric(10,6) UNSIGNED DEFAULT 1,\n"
        + "`c_dec_df` dec DEFAULT 10234273,\n"
        + "`c_dec_10` dec(10,5) DEFAULT 1,\n"
        + "`c_dec_df_un` dec UNSIGNED DEFAULT 10234273,\n"
        + "`c_dec_un` dec(10,6) UNSIGNED DEFAULT 1,\n"
        + "`c_float` float DEFAULT 9.1096275E8,\n"
        + "`c_float_pr` float(10,3) DEFAULT -5839673.5,\n"
        + "`c_float_un` float(10,3) unsigned DEFAULT 2648.644,\n"
        + "`c_double` double DEFAULT 4.334081673614155E9,\n"
        + "`c_double_pr` double(10,3) DEFAULT 6973286.176,\n"
        + "`c_double_un` double(10,3) unsigned DEFAULT 7630560.182,\n"
        + "`c_date` date DEFAULT '2019-02-15' COMMENT 'date',\n"
        + "`c_datetime` datetime DEFAULT '2019-02-15 14:54:41',\n"
        + "`c_datetime_ms` datetime(3) DEFAULT '2019-02-15 14:54:41.789',\n"
        + "`c_datetime_df` datetime DEFAULT CURRENT_TIMESTAMP,\n"
        + "`c_timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,\n"
        + "`c_timestamp_2` timestamp DEFAULT '2020-12-29 12:27:30',\n"
        + "`c_time` time DEFAULT '20:12:46',\n"
        + "`c_time_3` time(3) DEFAULT '12:30',\n"
        + "`c_year` year DEFAULT '2019',\n"
        + "`c_year_4` year(4) DEFAULT '2029',\n"
        + "`c_char` char(50) DEFAULT 'sjdlfjsdljldfjsldfsd',\n"
        + "`c_char_df` char DEFAULT 'x',\n"
        + "`c_varchar` varchar(50) DEFAULT 'sjdlfjsldhgowuere',\n"
        + "`c_nchar` nchar(100) DEFAULT '你好',\n"
        + "`c_nvarchar` nvarchar(100) DEFAULT '北京',\n"
        + "`c_binary_df` binary DEFAULT 'x',\n"
        + "`c_binary` binary(200) DEFAULT 'qoeuroieshdfs',\n"
        + "`c_varbinary` varbinary(200) DEFAULT 'sdfjsljlewwfs',\n"
        + "`c_blob_tiny` tinyblob DEFAULT NULL,\n"
        + "`c_blob` blob DEFAULT NULL,\n"
        + "`c_blob_medium` mediumblob DEFAULT NULL,\n"
        + "`c_blob_long` longblob DEFAULT NULL,\n"
        + "`c_text_tiny` tinytext DEFAULT NULL,\n"
        + "`c_text` text DEFAULT NULL,\n"
        + "`c_text_medium` mediumtext DEFAULT NULL,\n"
        + "`c_text_long` longtext DEFAULT NULL,\n"
        + "`c_enum` enum('a','b','c') DEFAULT 'a',\n"
        + "`c_enum_2` enum('x-small', 'small', 'medium', 'large', 'x-large') DEFAULT 'small',\n"
        + "`c_set` set('a','b','c') DEFAULT 'a',\n"
        + "`c_json` json DEFAULT NULL,\n"
        + "`c_geo` geometry DEFAULT NULL,"
        + "`c_idx` bigint not null default 100,"
        + "PRIMARY KEY (`id`)) dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 3";

    public static final String T_RANDOM_INSERT_SQL =
        "INSERT `%s` ("
            + "c_char , \n"
            + "c_varchar , \n"
            + "c_binary , \n"
            + "c_varbinary , \n"
            + "c_blob_tiny , \n"
            + "c_blob , \n"
            + "c_blob_medium , \n"
            + "c_blob_long , \n"
            + "c_text_tiny , \n"
            + "c_text , \n"
            + "c_text_medium , \n"
            + "c_text_long , \n"
            + "c_json , \n"
            + "c_geo \n"
            + ") VALUES( \n"
            + "'select \\n from t\\n where t.name = \"你好\"',\n"
            + "'int a = max(4,5)',\n"
            + "'select \\n from t\\n where name = \"你好\" \\nand age = max(4,5)',\n"
            + "'varchar a = \uD83D\uDE0D\uD83D\uDE0D',\n"
            + "'select \\n from t\\n where name = \"你好\"',\n"
            + "'varchar a = \uD83D\uDE0D\uD83D\uDE0D',\n"
            + "'select t.name,t.age,t.salary 13\\n from employee t\\n where t.region <> \"杭州\"\\n and\\n t.nickname = \\\uD83D\uDE0D\"(此处省略300字)\";',\n"
            + "'int a = max(4,5)',\n"
            + "'你好',\n"
            + "'select \\n from t\\n where name = \"你好\"',\n"
            + "'select t.name,t.age,t.salary 13\\n from employee t\\n where t.region <> \"杭州\"\\n and\\n t.nickname = \"(此处省略300字)\";',\n"
            + "'int a = max(4,5)' ,\n"
            + "'{}' ,\n "
            + "ST_GeomFromText('POINT(" + RandomUtils.nextFloat() + " " + RandomUtils.nextFloat() + ")')"
            + ")";

    public static final String T_RANDOM_QUERY_SQL = "select * from `%s`";

    public static void main(String args[]) {
        System.out.println(T_RANDOM_CREATE_SQL);
    }
}
